package com.houdoudou.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.houdoudou.bean.Customer;
import com.houdoudou.bean.Distribute;

public class DistributeDao {

	 Connection conn=null;

	public DistributeDao(Connection conn) {
		super();
		this.conn = conn;
	}
	
	 public void create(List<Distribute> distributes) throws SQLException{
			
		   PreparedStatement pstm=null; 
		   Statement  stm=null;
		   ResultSet rs=null; 	
		   String sql=null;
		   
		   try{
			   for(int i=0;i<distributes.size();i++){
			   sql = "replace  into distribute (machine,supplier,timeFlag,timeAA,owner) "
		       			+ "values (?,?,?,?,?)";	  
		       	pstm=conn.prepareStatement(sql);
		    	pstm.setInt(1, distributes.get(i).getMachine());
		       	pstm.setString(2, distributes.get(i).getSupplier());	       
		     	pstm.setInt(3, distributes.get(i).getTimeFlag());	
		    	pstm.setString(4, distributes.get(i).getTimeAA());
		    	pstm.setString(5, distributes.get(i).getOwner());
		       	pstm.executeUpdate();}
		   }finally{
			   ConnFactory.closeStatment(pstm, pstm, rs);
		   }
	     }		
			
	   
	   public List<Distribute> search(String sql) throws SQLException {
		   List<Distribute> distributes= new ArrayList<Distribute>();
		   PreparedStatement pstm=null; 
		   Statement  stm=null;
		   ResultSet rs=null; 	

	        try {
	        		        	
				stm=conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
				rs=stm.executeQuery(sql);
				while(rs.next()){
					Distribute distribute= new Distribute();
					distribute.setMachine(rs.getInt("machine"));
					distribute.setSupplier(rs.getString("supplier"));
					distribute.setTimeFlag(rs.getInt("timeFlag"));	
					distribute.setTimeAA(rs.getString("timeAA"));	
					distribute.setOwner(rs.getString("owner"));
					distributes.add(distribute);
					Distribute.print("when search ", distribute);
				}
	        }finally{
	        	ConnFactory.closeStatment(pstm, pstm, rs);
	        }
		   
		   return distributes;
	   }
	
	   public  void delete(String sql) throws SQLException{
		   PreparedStatement pstm=null; 
		   Statement  stm=null;
		   ResultSet rs=null; 	
		 
	        try {     			        	
				stm=conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
				stm.executeUpdate(sql);		
			 } finally{
				ConnFactory.closeStatment(pstm, pstm, rs);
			}		
	   }
	   
	   
	   public static  String  getSearchSql(Distribute distribute){
			String sql="select * from distribute ";
			
			int count=0;		
				if(!distribute.getSupplier().isEmpty()) {	
					if(count==0) {sql= sql+" where supplier = "+distribute.getSupplier();
					}else{
						sql= sql+" and  supplier = "+distribute.getSupplier();
					  }  
					  count++; }
				
				if(distribute.getMachine()!=0) {	
					if(count==0) {sql= sql+" where machine = "+distribute.getMachine();
					}else{
						sql= sql+" and  machine = "+distribute.getMachine();
					  }  
					  count++; }
				if(distribute.getTimeFlag()!=0) {	
					if(count==0) {sql= sql+" where timeFlag = "+distribute.getTimeFlag();
					}else{
						sql= sql+" and  timeFlag = "+distribute.getTimeFlag();
					  }  
					  count++; }
				if(!distribute.getOwner().isEmpty()) {	
					if(count==0) {sql= sql+" where owner = "+distribute.getOwner();
					}else{
						sql= sql+" and  owner = "+distribute.getOwner();
					  }  
					  count++; }
			sql=sql+" order by supplier";
			System.out.println(sql);
			return sql;
		}
		

		public static  String  getDeleteSql(Distribute distribute){
			String sql="select * from distribute";
		  sql = "delete  from distribute ";
		  int count=0;
		  
			if(!distribute.getSupplier().isEmpty()) {	
				if(count==0) {sql= sql+" where supplier = "+distribute.getSupplier();
				}else{
					sql= sql+" and  supplier = "+distribute.getSupplier();
				  }  
				  count++; }
			
			if(distribute.getMachine()!=0) {	
				if(count==0) {sql= sql+" where machine = "+distribute.getMachine();
				}else{
					sql= sql+" and  machine = "+distribute.getMachine();
				  }  
				  count++; }
			if(distribute.getTimeFlag()!=0) {	
				if(count==0) {sql= sql+" where timeFlag = "+distribute.getTimeFlag();
				}else{
					sql= sql+" and  timeFlag = "+distribute.getTimeFlag();
				  }  
				  count++; }
				 return sql;
		}
}
